const express = require('express');
const cors = require('cors');
const mysql = require('mysql2/promise');
const app = express();
const port = 3000;

// MySQL数据库连接配置
const pool = mysql.createPool({
    host: 'localhost',
    user: 'root',
    password: '123456',
    database: 'bank-system',
    port: 3306,
    waitForConnections: true,
    connectionLimit: 10,
    queueLimit: 0
});

// 中间件
app.use(cors());
app.use(express.json());

// 辅助函数：处理查询参数
function buildSearchQuery(baseQuery, searchParams, tableAlias = '') {
    const conditions = [];
    const values = [];
    
    for (const [key, value] of Object.entries(searchParams)) {
        if (value !== undefined && value !== null && value !== '') {
            const column = tableAlias ? `${tableAlias}.${key}` : key;
            if (typeof value === 'string') {
                // 模糊查询
                conditions.push(`${column} LIKE ?`);
                values.push(`%${value}%`);
            } else {
                // 精确查询
                conditions.push(`${column} = ?`);
                values.push(value);
            }
        }
    }
    
    if (conditions.length > 0) {
        return {
            query: `${baseQuery} WHERE ${conditions.join(' AND ')}`,
            values
        };
    }
    
    return {
        query: baseQuery,
        values
    };
}

// 辅助函数：将JSON转换为CSV
function convertToCsv(data) {
    if (!data || data.length === 0) {
        return '';
    }
    const replacer = (key, value) => value === null ? '' : value;
    const header = Object.keys(data[0]);
    const csvRows = [
        header.join(','),
        ...data.map(row => 
            header.map(fieldName => JSON.stringify(row[fieldName], replacer)).join(',')
        )
    ];
    return csvRows.join('\r\n');
}

// API路由 - 客户管理
app.get('/api/customers', async (req, res) => {
    try {
        let where = ' WHERE 1=1';
        const params = [];
        if (req.query.q) {
            where += ' AND (customerName LIKE ? OR PID LIKE ?)';
            params.push(`%${req.query.q}%`, `%${req.query.q}%`);
        }
        
        const page = parseInt(req.query.page) || 1;
        const pageSize = parseInt(req.query.pageSize) || 10;

        // 查询总数
        const [countRows] = await pool.execute(
            'SELECT COUNT(*) as total FROM userInfo' + where,
            params
        );
        const total = countRows[0].total;

        // 查询数据
        let sql = 'SELECT * FROM userInfo' + where + ' LIMIT ?, ?';
        const dataParams = params.concat([(page - 1) * pageSize, pageSize]);
        const [rows] = await pool.execute(sql, dataParams);

        res.json({
            data: rows,
            total: total
        });
    } catch (error) {
        res.status(500).json({ error: error.message });
    }
});

app.get('/api/customers/export', async (req, res) => {
    try {
        let where = ' WHERE 1=1';
        const params = [];
        if (req.query.q) {
            where += ' AND (customerName LIKE ? OR PID LIKE ?)';
            params.push(`%${req.query.q}%`, `%${req.query.q}%`);
        }

        const [rows] = await pool.execute('SELECT * FROM userInfo' + where, params);
        
        const csv = convertToCsv(rows);
        res.header('Content-Type', 'text/csv; charset=utf-8');
        res.attachment('customers.csv');
        res.send(csv);
    } catch (error) {
        res.status(500).json({ error: error.message });
    }
});

app.get('/api/customers/:id', async (req, res) => {
    try {
        const [rows] = await pool.execute(
            'SELECT * FROM userInfo WHERE customerID = ?', 
            [req.params.id]
        );
        res.json(rows[0] || {});
    } catch (error) {
        res.status(500).json({ error: error.message });
    }
});

app.post('/api/customers', async (req, res) => {
    const { customerName, PID, telephone, address } = req.body;
    try {
        // 验证身份证号是否已存在
        const [existingCustomers] = await pool.execute(
            'SELECT * FROM userInfo WHERE PID = ?',
            [PID]
        );
        
        if (existingCustomers.length > 0) {
            return res.status(400).json({ error: '该身份证号已存在' });
        }
        
        const [result] = await pool.execute(
            'INSERT INTO userInfo (customerName, PID, telephone, address) VALUES (?, ?, ?, ?)',
            [customerName, PID, telephone, address]
        );
        
        const [rows] = await pool.execute(
            'SELECT * FROM userInfo WHERE customerID = ?', 
            [result.insertId]
        );
        
        res.status(201).json(rows[0]);
    } catch (error) {
        res.status(500).json({ error: error.message });
    }
});

app.put('/api/customers/:id', async (req, res) => {
    const { customerName, PID, telephone, address } = req.body;
    try {
        // 检查客户是否存在
        const [existingCustomers] = await pool.execute(
            'SELECT * FROM userInfo WHERE customerID = ?',
            [req.params.id]
        );
        
        if (existingCustomers.length === 0) {
            return res.status(404).json({ error: '客户不存在' });
        }
        
        // 如果修改了身份证号，检查新身份证号是否已存在
        if (PID && PID !== existingCustomers[0].PID) {
            const [idCheck] = await pool.execute(
                'SELECT * FROM userInfo WHERE PID = ?',
                [PID]
            );
            
            if (idCheck.length > 0) {
                return res.status(400).json({ error: '该身份证号已被其他客户使用' });
            }
        }
        
        const [result] = await pool.execute(
            'UPDATE userInfo SET customerName = ?, PID = ?, telephone = ?, address = ? WHERE customerID = ?',
            [customerName, PID, telephone, address, req.params.id]
        );
        
        const [rows] = await pool.execute(
            'SELECT * FROM userInfo WHERE customerID = ?', 
            [req.params.id]
        );
        
        res.json(rows[0]);
    } catch (error) {
        res.status(500).json({ error: error.message });
    }
});

app.delete('/api/customers/:id', async (req, res) => {
    try {
        // 检查客户是否存在
        const [existingCustomers] = await pool.execute(
            'SELECT * FROM userInfo WHERE customerID = ?',
            [req.params.id]
        );
        
        if (existingCustomers.length === 0) {
            return res.status(404).json({ error: '客户不存在' });
        }
        
        // 开启事务删除关联数据
        const connection = await pool.getConnection();
        try {
            await connection.beginTransaction();
            
            // 删除关联的交易记录
            await connection.execute(
                'DELETE FROM tradeInfo WHERE cardID IN (SELECT cardID FROM cardInfo WHERE customerID = ?)',
                [req.params.id]
            );
            
            // 删除关联的银行卡
            await connection.execute('DELETE FROM cardInfo WHERE customerID = ?', [req.params.id]);
            
            // 删除客户
            await connection.execute('DELETE FROM userInfo WHERE customerID = ?', [req.params.id]);
            
            await connection.commit();
            res.json({ success: true });
        } catch (error) {
            await connection.rollback();
            throw error;
        } finally {
            connection.release();
        }
    } catch (error) {
        res.status(500).json({ error: error.message });
    }
});

// API路由 - 银行卡管理
app.get('/api/cards', async (req, res) => {
    try {
        let baseSql = 'SELECT c.*, u.customerName FROM cardInfo c JOIN userInfo u ON c.customerID = u.customerID';
        let where = '';
        const params = [];
        
        if (req.query.q) {
            where = ' WHERE (c.cardID LIKE ? OR u.customerName LIKE ?)';
            params.push(`%${req.query.q}%`, `%${req.query.q}%`);
        }

        const page = parseInt(req.query.page) || 1;
        const pageSize = parseInt(req.query.pageSize) || 10;

        // 查询总数
        const countSql = `SELECT COUNT(*) as total FROM cardInfo c JOIN userInfo u ON c.customerID = u.customerID ${where}`;
        const [countRows] = await pool.execute(countSql, params);
        const total = countRows[0].total;

        // 查询数据
        let sql = `${baseSql} ${where} LIMIT ?, ?`;
        const dataParams = params.concat([(page - 1) * pageSize, pageSize]);
        const [rows] = await pool.execute(sql, dataParams);

        res.json({
            data: rows,
            total: total
        });
    } catch (error) {
        res.status(500).json({ error: error.message });
    }
});

app.get('/api/cards/export', async (req, res) => {
    try {
        let baseSql = 'SELECT c.*, u.customerName FROM cardInfo c JOIN userInfo u ON c.customerID = u.customerID';
        let where = '';
        const params = [];
        
        if (req.query.q) {
            where = ' WHERE (c.cardID LIKE ? OR u.customerName LIKE ?)';
            params.push(`%${req.query.q}%`, `%${req.query.q}%`);
        }

        const [rows] = await pool.execute(`${baseSql} ${where}`, params);

        const csv = convertToCsv(rows);
        res.header('Content-Type', 'text/csv; charset=utf-8');
        res.attachment('cards.csv');
        res.send(csv);
    } catch (error) {
        res.status(500).json({ error: error.message });
    }
});

app.get('/api/cards/:id', async (req, res) => {
    try {
        // 关联查询客户姓名和存款类型
        const [rows] = await pool.execute(`
            SELECT c.*, u.customerName, d.savingName 
            FROM cardInfo c
            JOIN userInfo u ON c.customerID = u.customerID
            JOIN deposit d ON c.savingID = d.savingID
            WHERE c.cardID = ?
        `, [req.params.id]);
        
        res.json(rows[0] || {});
    } catch (error) {
        res.status(500).json({ error: error.message });
    }
});

app.post('/api/cards', async (req, res) => {
    const { customerID, savingID, openMoney, pass, IsReportLoss = '正常' } = req.body;
    try {
        // 生成随机卡号
        const cardID = '10103576' + Math.floor(10000000 + Math.random() * 90000000);
        
        // 检查客户是否存在
        const [customers] = await pool.execute(
            'SELECT * FROM userInfo WHERE customerID = ?',
            [customerID]
        );
        
        if (customers.length === 0) {
            return res.status(400).json({ error: '客户不存在' });
        }
        
        // 检查存款类型是否存在
        const [savings] = await pool.execute(
            'SELECT * FROM deposit WHERE savingID = ?',
            [savingID]
        );
        
        if (savings.length === 0) {
            return res.status(400).json({ error: '存款类型不存在' });
        }
        
        const [result] = await pool.execute(
            'INSERT INTO cardInfo (cardID, curID, savingID, openDate, openMoney, balance, pass, IsReportLoss, customerID) VALUES (?, "RMB", ?, NOW(), ?, ?, ?, ?, ?)',
            [cardID, savingID, openMoney, openMoney, pass, IsReportLoss, customerID]
        );
        
        const [rows] = await pool.execute(
            'SELECT * FROM cardInfo WHERE cardID = ?', 
            [cardID]
        );
        
        res.status(201).json(rows[0]);
    } catch (error) {
        res.status(500).json({ error: error.message });
    }
});

app.put('/api/cards/:id', async (req, res) => {
    const { customerID, savingID, openMoney, balance, pass, IsReportLoss } = req.body;
    try {
        // 检查银行卡是否存在
        const [existingCards] = await pool.execute(
            'SELECT * FROM cardInfo WHERE cardID = ?',
            [req.params.id]
        );
        
        if (existingCards.length === 0) {
            return res.status(404).json({ error: '银行卡不存在' });
        }
        
        // 如果修改了客户ID，检查客户是否存在
        if (customerID && customerID !== existingCards[0].customerID) {
            const [customers] = await pool.execute(
                'SELECT * FROM userInfo WHERE customerID = ?',
                [customerID]
            );
            
            if (customers.length === 0) {
                return res.status(400).json({ error: '客户不存在' });
            }
        }
        
        // 如果修改了存款类型，检查存款类型是否存在
        if (savingID && savingID !== existingCards[0].savingID) {
            const [savings] = await pool.execute(
                'SELECT * FROM deposit WHERE savingID = ?',
                [savingID]
            );
            
            if (savings.length === 0) {
                return res.status(400).json({ error: '存款类型不存在' });
            }
        }
        
        let updateQuery = 'UPDATE cardInfo SET customerID = ?, savingID = ?, openMoney = ?, balance = ?, IsReportLoss = ?';
        const queryParams = [customerID, savingID, openMoney, balance, IsReportLoss];

        if (pass) {
            updateQuery += ', pass = ?';
            queryParams.push(pass);
        }

        updateQuery += ' WHERE cardID = ?';
        queryParams.push(req.params.id);

        await pool.execute(updateQuery, queryParams);
        
        const [rows] = await pool.execute(
            'SELECT * FROM cardInfo WHERE cardID = ?', 
            [req.params.id]
        );
        
        res.json(rows[0]);
    } catch (error) {
        res.status(500).json({ error: error.message });
    }
});

app.delete('/api/cards/:id', async (req, res) => {
    try {
        // 检查银行卡是否存在
        const [existingCards] = await pool.execute(
            'SELECT * FROM cardInfo WHERE cardID = ?',
            [req.params.id]
        );
        
        if (existingCards.length === 0) {
            return res.status(404).json({ error: '银行卡不存在' });
        }
        
        // 开启事务删除关联数据
        const connection = await pool.getConnection();
        try {
            await connection.beginTransaction();
            
            // 删除关联的交易记录
            await connection.execute('DELETE FROM tradeInfo WHERE cardID = ?', [req.params.id]);
            
            // 删除银行卡
            await connection.execute('DELETE FROM cardInfo WHERE cardID = ?', [req.params.id]);
            
            await connection.commit();
            res.json({ success: true });
        } catch (error) {
            await connection.rollback();
            throw error;
        } finally {
            connection.release();
        }
    } catch (error) {
        res.status(500).json({ error: error.message });
    }
});

// API路由 - 存款业务管理
app.get('/api/savings', async (req, res) => {
    try {
        let where = ' WHERE 1=1';
        const params = [];
        if (req.query.q) {
            where += ' AND (savingName LIKE ? OR descrip LIKE ?)';
            params.push(`%${req.query.q}%`, `%${req.query.q}%`);
        }
        const page = parseInt(req.query.page) || 1;
        const pageSize = parseInt(req.query.pageSize) || 10;
        // 查询总数
        const [countRows] = await pool.execute(
            'SELECT COUNT(*) as total FROM deposit' + where,
            params
        );
        const total = countRows[0].total;
        // 查询数据
        let sql = 'SELECT savingID, savingName, descrip AS description FROM deposit' + where + ' LIMIT ?, ?';
        const dataParams = params.concat([(page - 1) * pageSize, pageSize]);
        const [rows] = await pool.execute(sql, dataParams);
        res.json({
            data: rows,
            total: total
        });
    } catch (error) {
        res.status(500).json({ error: error.message });
    }
});

app.get('/api/savings/export', async (req, res) => {
    try {
        let where = ' WHERE 1=1';
        const params = [];
        if (req.query.q) {
            where += ' AND (savingName LIKE ? OR descrip LIKE ?)';
            params.push(`%${req.query.q}%`, `%${req.query.q}%`);
        }
        const [rows] = await pool.execute('SELECT savingID, savingName, descrip AS description FROM deposit' + where, params);
        const csv = convertToCsv(rows);
        res.header('Content-Type', 'text/csv; charset=utf-8');
        res.attachment('savings.csv');
        res.send(csv);
    } catch (error) {
        res.status(500).json({ error: error.message });
    }
});

app.get('/api/savings/:id', async (req, res) => {
    try {
        const [rows] = await pool.execute(
            'SELECT savingID, savingName, descrip AS description FROM deposit WHERE savingID = ?',
            [req.params.id]
        );
        res.json(rows[0] || {});
    } catch (error) {
        res.status(500).json({ error: error.message });
    }
});

app.post('/api/savings', async (req, res) => {
    const { savingID, savingName, description } = req.body;
    try {
        // 检查业务ID或名称是否已存在
        const [existingSavings] = await pool.execute(
            'SELECT * FROM deposit WHERE savingID = ? OR savingName = ?',
            [savingID, savingName]
        );
        if (existingSavings.length > 0) {
            return res.status(400).json({ error: '业务ID或名称已存在' });
        }
        const [result] = await pool.execute(
            'INSERT INTO deposit (savingID, savingName, descrip) VALUES (?, ?, ?)',
            [savingID, savingName, description]
        );
        const [rows] = await pool.execute(
            'SELECT savingID, savingName, descrip AS description FROM deposit WHERE savingID = ?',
            [savingID]
        );
        res.status(201).json(rows[0]);
    } catch (error) {
        res.status(500).json({ error: error.message });
    }
});

app.put('/api/savings/:id', async (req, res) => {
    const { savingName, description } = req.body;
    try {
        // 检查存款业务是否存在
        const [existingSavings] = await pool.execute(
            'SELECT * FROM deposit WHERE savingID = ?',
            [req.params.id]
        );
        if (existingSavings.length === 0) {
            return res.status(404).json({ error: '存款业务不存在' });
        }
        // 检查新名称是否已存在（排除自己）
        if (savingName && savingName !== existingSavings[0].savingName) {
            const [nameCheck] = await pool.execute(
                'SELECT * FROM deposit WHERE savingName = ? AND savingID != ?',
                [savingName, req.params.id]
            );
            if (nameCheck.length > 0) {
                return res.status(400).json({ error: '该存款业务名称已被使用' });
            }
        }
        await pool.execute(
            'UPDATE deposit SET savingName = ?, descrip = ? WHERE savingID = ?',
            [savingName, description, req.params.id]
        );
        const [rows] = await pool.execute(
            'SELECT savingID, savingName, descrip AS description FROM deposit WHERE savingID = ?',
            [req.params.id]
        );
        res.json(rows[0]);
    } catch (error) {
        res.status(500).json({ error: error.message });
    }
});

app.delete('/api/savings/:id', async (req, res) => {
    try {
        // 检查存款业务是否存在
        const [existingSavings] = await pool.execute(
            'SELECT * FROM deposit WHERE savingID = ?',
            [req.params.id]
        );
        
        if (existingSavings.length === 0) {
            return res.status(404).json({ error: '存款业务不存在' });
        }
        
        // 检查是否有关联的银行卡
        const [cards] = await pool.execute(
            'SELECT * FROM cardInfo WHERE savingID = ?',
            [req.params.id]
        );
        
        if (cards.length > 0) {
            return res.status(400).json({ error: '该存款业务下存在关联的银行卡，无法删除' });
        }
        
        await pool.execute('DELETE FROM deposit WHERE savingID = ?', [req.params.id]);
        res.json({ success: true });
    } catch (error) {
        res.status(500).json({ error: error.message });
    }
});

// API路由 - 交易记录管理
app.get('/api/transactions', async (req, res) => {
    try {
        let where = ' WHERE 1=1';
        const params = [];
        if (req.query.q) {
            where += ' AND (cardID LIKE ? OR tradeType LIKE ?)';
            params.push(`%${req.query.q}%`, `%${req.query.q}%`);
        }
        if (req.query.tradeDate) {
            where += ' AND DATE(tradeDate) = ?';
            params.push(req.query.tradeDate);
        }
        const page = parseInt(req.query.page) || 1;
        const pageSize = parseInt(req.query.pageSize) || 10;

        // 查询总数
        const [countRows] = await pool.execute(
            'SELECT COUNT(*) as total FROM tradeInfo' + where,
            params
        );
        const total = countRows[0].total;

        // 查询数据
        let sql = 'SELECT * FROM tradeInfo' + where + ' ORDER BY tradeDate DESC LIMIT ?, ?';
        const dataParams = params.concat([(page - 1) * pageSize, pageSize]);
        const [rows] = await pool.execute(sql, dataParams);

        res.json({
            data: rows,
            total: total
        });
    } catch (error) {
        res.status(500).json({ error: error.message });
    }
});

app.get('/api/transactions/export', async (req, res) => {
    try {
        let where = ' WHERE 1=1';
        const params = [];
        if (req.query.q) {
            where += ' AND (cardID LIKE ? OR tradeType LIKE ?)';
            params.push(`%${req.query.q}%`, `%${req.query.q}%`);
        }
        if (req.query.tradeDate) {
            where += ' AND DATE(tradeDate) = ?';
            params.push(req.query.tradeDate);
        }
        
        const [rows] = await pool.execute('SELECT * FROM tradeInfo' + where + ' ORDER BY tradeDate DESC', params);
        
        const csv = convertToCsv(rows);
        res.header('Content-Type', 'text/csv; charset=utf-8');
        res.attachment('transactions.csv');
        res.send(csv);
    } catch (error) {
        res.status(500).json({ error: error.message });
    }
});

// 客户模块 - 余额查询
app.post('/api/customer/balance', async (req, res) => {
    const { cardID, pass } = req.body;
    try {
        if (!cardID || !pass) {
            return res.status(400).json({ error: '银行卡号和密码不能为空' });
        }

        const [rows] = await pool.execute(
            'SELECT balance, IsReportLoss, pass FROM cardInfo WHERE cardID = ?',
            [cardID]
        );
        
        if (rows.length === 0) {
            return res.status(404).json({ error: '银行卡不存在' });
        }
        
        if (rows[0].IsReportLoss === '挂失') {
            return res.status(403).json({ error: '该银行卡已挂失，无法操作' });
        }

        if (rows[0].pass !== pass) {
            return res.status(401).json({ error: '密码错误' });
        }
        
        res.json({ balance: rows[0].balance });
    } catch (error) {
        console.error('Balance query error:', error);
        res.status(500).json({ error: '服务器内部错误' });
    }
});

// 客户模块 - 交易查询
app.get('/api/customer/transactions/:cardId', async (req, res) => {
    try {
        const { page = 1, pageSize = 10 } = req.query;
        const offset = (parseInt(page) - 1) * parseInt(pageSize);
        
        // 检查银行卡是否存在
        const [cardCheck] = await pool.execute(
            'SELECT * FROM cardInfo WHERE cardID = ?',
            [req.params.cardId]
        );
        
        if (cardCheck.length === 0) {
            return res.status(404).json({ error: '银行卡不存在' });
        }
        
        if (cardCheck[0].IsReportLoss === '挂失') {
            return res.status(403).json({ error: '该银行卡已挂失，无法查询交易记录' });
        }
        
        // 查询交易记录
        const [rows] = await pool.execute(
            'SELECT * FROM tradeInfo WHERE cardID = ? ORDER BY tradeDate DESC LIMIT ? OFFSET ?',
            [req.params.cardId, parseInt(pageSize), offset]
        );
        
        // 获取总记录数
        const [countResult] = await pool.execute(
            'SELECT COUNT(*) as total FROM tradeInfo WHERE cardID = ?',
            [req.params.cardId]
        );
        
        res.json({
            data: rows,
            total: countResult[0].total,
            page: parseInt(page),
            pageSize: parseInt(pageSize)
        });
    } catch (error) {
        res.status(500).json({ error: error.message });
    }
});

// 客户模块 - 存款
app.post('/api/customer/deposit', async (req, res) => {
    const { cardID, amount, pass } = req.body;
    try {
        if (amount <= 0) {
            return res.status(400).json({ error: '存款金额必须大于0' });
        }
        
        // 开启事务
        const connection = await pool.getConnection();
        try {
            await connection.beginTransaction();
            
            // 验证银行卡和密码
            const [cards] = await connection.execute(
                'SELECT * FROM cardInfo WHERE cardID = ? AND pass = ?',
                [cardID, pass]
            );
            
            if (cards.length === 0) {
                await connection.rollback();
                return res.status(400).json({ error: '银行卡号或密码错误' });
            }
            
            if (cards[0].IsReportLoss === '挂失') {
                await connection.rollback();
                return res.status(403).json({ error: '该银行卡已挂失，无法进行存款操作' });
            }
            
            // 记录存款交易
            await connection.execute(
                'INSERT INTO tradeInfo (tradeDate, tradeType, cardID, tradeMoney, remark) VALUES (NOW(), ?, ?, ?, ?)',
                ['存款', cardID, amount, '存款操作']
            );
            
            // 更新余额
            await connection.execute(
                'UPDATE cardInfo SET balance = balance + ? WHERE cardID = ?',
                [amount, cardID]
            );
            
            await connection.commit();
            res.json({ success: true, message: '存款成功' });
        } catch (error) {
            await connection.rollback();
            throw error;
        } finally {
            connection.release();
        }
    } catch (error) {
        res.status(500).json({ error: error.message });
    }
});

// 客户模块 - 取款
app.post('/api/customer/withdraw', async (req, res) => {
    const { cardID, amount, pass } = req.body;
    try {
        if (amount <= 0) {
            return res.status(400).json({ error: '取款金额必须大于0' });
        }
        
        // 开启事务
        const connection = await pool.getConnection();
        try {
            await connection.beginTransaction();
            
            // 验证银行卡和密码
            const [cards] = await connection.execute(
                'SELECT * FROM cardInfo WHERE cardID = ? AND pass = ?',
                [cardID, pass]
            );
            
            if (cards.length === 0) {
                await connection.rollback();
                return res.status(400).json({ error: '银行卡号或密码错误' });
            }
            
            if (cards[0].IsReportLoss === '挂失') {
                await connection.rollback();
                return res.status(403).json({ error: '该银行卡已挂失，无法进行取款操作' });
            }
            
            // 检查余额
            if (cards[0].balance < amount) {
                await connection.rollback();
                return res.status(400).json({ error: '余额不足，无法完成取款' });
            }
            
            // 记录取款交易
            await connection.execute(
                'INSERT INTO tradeInfo (tradeDate, tradeType, cardID, tradeMoney, remark) VALUES (NOW(), ?, ?, ?, ?)',
                ['取款', cardID, -amount, '取款操作']
            );
            
            // 更新余额
            await connection.execute(
                'UPDATE cardInfo SET balance = balance - ? WHERE cardID = ?',
                [amount, cardID]
            );
            
            await connection.commit();
            res.json({ success: true, message: '取款成功' });
        } catch (error) {
            await connection.rollback();
            throw error;
        } finally {
            connection.release();
        }
    } catch (error) {
        res.status(500).json({ error: error.message });
    }
});

// 客户模块 - 挂失/解挂
app.post('/api/customer/report', async (req, res) => {
    const { cardID, pass, action } = req.body; // action: 'report' 挂失, 'cancel' 解挂
    try {
        // 开启事务
        const connection = await pool.getConnection();
        try {
            await connection.beginTransaction();
            
            // 验证银行卡和密码
            const [cards] = await connection.execute(
                'SELECT * FROM cardInfo WHERE cardID = ? AND pass = ?',
                [cardID, pass]
            );
            
            if (cards.length === 0) {
                await connection.rollback();
                return res.status(400).json({ error: '银行卡号或密码错误' });
            }
            
            let newStatus, actionText;
            if (action === 'report') {
                if (cards[0].IsReportLoss === '挂失') {
                    await connection.rollback();
                    return res.status(400).json({ error: '该银行卡已处于挂失状态' });
                }
                newStatus = '挂失';
                actionText = '挂失';
            } else if (action === 'cancel') {
                if (cards[0].IsReportLoss === '正常') {
                    await connection.rollback();
                    return res.status(400).json({ error: '该银行卡未挂失，无需解挂' });
                }
                newStatus = '正常';
                actionText = '解挂';
            } else {
                await connection.rollback();
                return res.status(400).json({ error: '无效的操作类型' });
            }
            
            // 更新卡状态
            await connection.execute(
                'UPDATE cardInfo SET IsReportLoss = ? WHERE cardID = ?',
                [newStatus, cardID]
            );
            
            // 记录操作
            await connection.execute(
                'INSERT INTO tradeInfo (tradeDate, tradeType, cardID, tradeMoney, remark) VALUES (NOW(), ?, ?, ?, ?)',
                [actionText, cardID, 0, `${actionText}操作`]
            );
            
            await connection.commit();
            res.json({ success: true, message: `${actionText}成功` });
        } catch (error) {
            await connection.rollback();
            throw error;
        } finally {
            connection.release();
        }
    } catch (error) {
        res.status(500).json({ error: error.message });
    }
});

// 客户模块 - 修改密码
app.post('/api/customer/changePassword', async (req, res) => {
    const { cardID, oldPass, newPass } = req.body;
    try {
        // 验证新旧密码
        if (oldPass === newPass) {
            return res.status(400).json({ error: '新密码不能与旧密码相同' });
        }
        
        // 开启事务
        const connection = await pool.getConnection();
        try {
            await connection.beginTransaction();
            
            // 验证银行卡和旧密码
            const [cards] = await connection.execute(
                'SELECT * FROM cardInfo WHERE cardID = ? AND pass = ?',
                [cardID, oldPass]
            );
            
            if (cards.length === 0) {
                await connection.rollback();
                return res.status(400).json({ error: '银行卡号或旧密码错误' });
            }
            
            if (cards[0].IsReportLoss === '挂失') {
                await connection.rollback();
                return res.status(403).json({ error: '该银行卡已挂失，无法修改密码' });
            }
            
            // 更新密码
            await connection.execute(
                'UPDATE cardInfo SET pass = ? WHERE cardID = ?',
                [newPass, cardID]
            );
            
            // 记录操作
            await connection.execute(
                'INSERT INTO tradeInfo (tradeDate, tradeType, cardID, tradeMoney, remark) VALUES (NOW(), ?, ?, ?, ?)',
                ['修改密码', cardID, 0, '修改密码操作']
            );
            
            await connection.commit();
            res.json({ success: true, message: '密码修改成功' });
        } catch (error) {
            await connection.rollback();
            throw error;
        } finally {
            connection.release();
        }
    } catch (error) {
        res.status(500).json({ error: error.message });
    }
});

// 客户模块 - 转账
app.post('/api/customer/transfer', async (req, res) => {
    const { fromCardID, toCardID, amount, pass } = req.body;
    try {
        if (!fromCardID || !toCardID || !amount || !pass) {
            return res.status(400).json({ error: '请填写所有字段' });
        }
        if (fromCardID === toCardID) {
            return res.status(400).json({ error: '不能给自己转账' });
        }
        if (amount <= 0) {
            return res.status(400).json({ error: '转账金额必须大于0' });
        }
        const connection = await pool.getConnection();
        try {
            await connection.beginTransaction();
            // 验证转出卡
            const [fromCards] = await connection.execute(
                'SELECT * FROM cardInfo WHERE cardID = ? AND pass = ?',
                [fromCardID, pass]
            );
            if (fromCards.length === 0) {
                await connection.rollback();
                return res.status(400).json({ error: '转出卡号或密码错误' });
            }
            if (fromCards[0].IsReportLoss === '挂失') {
                await connection.rollback();
                return res.status(403).json({ error: '转出卡已挂失，无法转账' });
            }
            if (fromCards[0].balance < amount) {
                await connection.rollback();
                return res.status(400).json({ error: '余额不足' });
            }
            // 验证收款卡
            const [toCards] = await connection.execute(
                'SELECT * FROM cardInfo WHERE cardID = ?',
                [toCardID]
            );
            if (toCards.length === 0) {
                await connection.rollback();
                return res.status(404).json({ error: '收款卡号不存在' });
            }
            if (toCards[0].IsReportLoss === '挂失') {
                await connection.rollback();
                return res.status(403).json({ error: '收款卡已挂失，无法转账' });
            }
            // 扣减转出卡余额
            await connection.execute(
                'UPDATE cardInfo SET balance = balance - ? WHERE cardID = ?',
                [amount, fromCardID]
            );
            // 增加收款卡余额
            await connection.execute(
                'UPDATE cardInfo SET balance = balance + ? WHERE cardID = ?',
                [amount, toCardID]
            );
            // 记录交易（转出）
            await connection.execute(
                'INSERT INTO tradeInfo (tradeDate, tradeType, cardID, tradeMoney, remark) VALUES (NOW(), ?, ?, ?, ?)',
                ['转出', fromCardID, -amount, `转账到${toCardID}`]
            );
            // 记录交易（转入）
            await connection.execute(
                'INSERT INTO tradeInfo (tradeDate, tradeType, cardID, tradeMoney, remark) VALUES (NOW(), ?, ?, ?, ?)',
                ['转入', toCardID, amount, `来自${fromCardID}`]
            );
            await connection.commit();
            res.json({ success: true, message: '转账成功' });
        } catch (error) {
            await connection.rollback();
            throw error;
        } finally {
            connection.release();
        }
    } catch (error) {
        res.status(500).json({ error: error.message });
    }
});

// ========== 报表统计与导出接口 ========== //
// 银行卡月交易额统计
app.get('/api/reports/card-transactions', async (req, res) => {
    try {
        const { month, cardID } = req.query;
        let where = 'WHERE 1=1';
        const params = [];
        if (month) {
            where += ' AND DATE_FORMAT(tradeDate, "%Y-%m") = ?';
            params.push(month);
        }
        if (cardID) {
            where += ' AND cardID = ?';
            params.push(cardID);
        }
        const [rows] = await pool.execute(
            `SELECT cardID, 
                    COUNT(*) as tradeCount, 
                    SUM(tradeMoney) as totalAmount 
             FROM tradeInfo ${where} 
             GROUP BY cardID`, params);
        res.json({ data: rows });
    } catch (error) {
        res.status(500).json({ error: error.message });
    }
});

// 月末汇总
app.get('/api/reports/month-summary', async (req, res) => {
    try {
        const { month } = req.query;
        let where = '';
        const params = [];
        if (month) {
            where = 'WHERE DATE_FORMAT(tradeDate, "%Y-%m") = ?';
            params.push(month);
        }
        const [rows] = await pool.execute(
            `SELECT 
                COUNT(DISTINCT cardID) as activeCards,
                SUM(CASE WHEN tradeType='存款' THEN tradeMoney ELSE 0 END) as totalDeposit,
                SUM(CASE WHEN tradeType='取款' THEN -tradeMoney ELSE 0 END) as totalWithdraw,
                SUM(tradeMoney) as totalAmount
             FROM tradeInfo ${where}`, params);
        res.json(rows[0]);
    } catch (error) {
        res.status(500).json({ error: error.message });
    }
});

// 导出报表
app.get('/api/reports/export', async (req, res) => {
    try {
        const { type, month } = req.query;
        let rows = [];
        if (type === 'card-transactions') {
            let where = 'WHERE 1=1';
            const params = [];
            if (month) {
                where += ' AND DATE_FORMAT(tradeDate, "%Y-%m") = ?';
                params.push(month);
            }
            [rows] = await pool.execute(
                `SELECT cardID, COUNT(*) as tradeCount, SUM(tradeMoney) as totalAmount 
                 FROM tradeInfo ${where} GROUP BY cardID`, params);
        } else if (type === 'month-summary') {
            let where = '';
            const params = [];
            if (month) {
                where = 'WHERE DATE_FORMAT(tradeDate, "%Y-%m") = ?';
                params.push(month);
            }
            [rows] = await pool.execute(
                `SELECT 
                    COUNT(DISTINCT cardID) as activeCards,
                    SUM(CASE WHEN tradeType='存款' THEN tradeMoney ELSE 0 END) as totalDeposit,
                    SUM(CASE WHEN tradeType='取款' THEN -tradeMoney ELSE 0 END) as totalWithdraw,
                    SUM(tradeMoney) as totalAmount
                 FROM tradeInfo ${where}`, params);
        }
        const csv = convertToCsv(rows);
        res.header('Content-Type', 'text/csv; charset=utf-8');
        res.attachment(`${type}-${month || 'all'}.csv`);
        res.send(csv);
    } catch (error) {
        res.status(500).json({ error: error.message });
    }
});

// 启动服务器
app.listen(port, () => {
    console.log(`服务器运行在 http://localhost:${port}`);
})